package com.efast.cafe.portal.service.query.impl;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


import com.efast.cafe.portal.service.query.defauleValue.UserConditionValue;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import com.efast.cafe.framework.util.QueryStringUtil;
import com.efast.cafe.framework.util.SqlConstants;
import com.efast.cafe.framework.util.SqlFormateUtil;
import com.efast.cafe.portal.bean.query.TableConditionBean;
import com.efast.cafe.portal.bean.query.TableOpBean;
import com.efast.cafe.portal.bean.query.TemplateObjBean;
import com.efast.cafe.portal.entity.query.TableView;
import com.efast.cafe.portal.entity.query.Template;
import com.efast.cafe.portal.service.query.ITemplateParseService;
import com.efast.cafe.portal.service.query.defauleValue.AbstractConditionValue;
import com.efast.cafe.portal.service.query.defauleValue.BaseConditionValue;
import com.efast.cafe.util.SetUtil;

import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.operators.conditional.AndExpression;
import net.sf.jsqlparser.expression.operators.conditional.OrExpression;
import net.sf.jsqlparser.expression.operators.relational.Between;
import net.sf.jsqlparser.expression.operators.relational.EqualsTo;
import net.sf.jsqlparser.expression.operators.relational.ExistsExpression;
import net.sf.jsqlparser.expression.operators.relational.GreaterThan;
import net.sf.jsqlparser.expression.operators.relational.GreaterThanEquals;
import net.sf.jsqlparser.expression.operators.relational.InExpression;
import net.sf.jsqlparser.expression.operators.relational.IsNullExpression;
import net.sf.jsqlparser.expression.operators.relational.LikeExpression;
import net.sf.jsqlparser.expression.operators.relational.MinorThan;
import net.sf.jsqlparser.expression.operators.relational.MinorThanEquals;
import net.sf.jsqlparser.expression.operators.relational.NotEqualsTo;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.select.FromItem;
import net.sf.jsqlparser.statement.select.Join;
import net.sf.jsqlparser.statement.select.LateralSubSelect;
import net.sf.jsqlparser.statement.select.OrderByElement;
import net.sf.jsqlparser.statement.select.PlainSelect;
import net.sf.jsqlparser.statement.select.Select;
import net.sf.jsqlparser.statement.select.SelectBody;
import net.sf.jsqlparser.statement.select.SelectItem;
import net.sf.jsqlparser.statement.select.SetOperationList;
import net.sf.jsqlparser.statement.select.SubJoin;
import net.sf.jsqlparser.statement.select.SubSelect;
import net.sf.jsqlparser.statement.select.ValuesList;
import net.sf.jsqlparser.statement.select.WithItem;
@Transactional
@RestController
public class TemplateParseServiceImpl implements ITemplateParseService{
	@SuppressWarnings({ "rawtypes", "static-access" })
	private static final Map<String, Class> map = BaseConditionValue.INSTANCE.map;

	@Override
	public TemplateObjBean parseSql(@RequestParam(name = "sql") String sql) {
		return parseSql(sql, false);
	}

	@Override
	public TemplateObjBean parseSql(@RequestBody Map<String, Object> sqlmap) {
		return parseSql((String) sqlmap.get("sql"));
	}

	@Override
	public synchronized TemplateObjBean parseSql(String sql, Boolean isPreview) {
		String message = SqlFormateUtil.doValidation(sql);
		sql = sql.replaceAll("\\#\\{", SqlFormateUtil.prefix);
		sql = sql.replaceAll("\\}", SqlFormateUtil.suffix);
		TemplateObjBean queryBean = new TemplateObjBean();
		if (!SqlFormateUtil.SUCCESS_MESSAGE.equals(message)) {
			queryBean.setMessage(message);
			queryBean.setSuccess(false);
			return queryBean;
		}
		try {
			String bindSql = SqlFormateUtil.format(sql);
			Statement statement = CCJSqlParserUtil.parse(bindSql);
			Select select = (Select) statement;
			SelectBody selectBody = select.getSelectBody();
			List<String> list = SqlFormateUtil.whereItems;
			queryBean= processSelectBody(selectBody);
			if (!isPreview) {
				queryBean.setTableConditions(getTableConditions(list));
				Template template = queryBean.getTemplate();
				template.setWhereArea(handelWhere(template.getWhereArea(),SqlFormateUtil.conditionItems));
				template.setFromArea(SqlFormateUtil.fromArea.trim());
				template.setSelectArea(SqlFormateUtil.selectArea.trim());
				template.setOrderbyArea(SqlFormateUtil.orderByArea.trim());
				template.setGroupbyArea(SqlFormateUtil.groupByArea.trim());
			}
			queryBean.setBindSql(SqlFormateUtil.replaceBack(bindSql));
			queryBean.setSuccess(true);
		} catch (JSQLParserException e) {
			queryBean.setMessage(e.getMessage());
			e.printStackTrace();
			return queryBean;
		}
		return queryBean;
	}


	public TemplateObjBean processSelectBody(SelectBody selectBody) {
		if (selectBody instanceof PlainSelect) {
			return processPlainSelect((PlainSelect) selectBody);
		} else if (selectBody instanceof WithItem) { //with A as (select * from tab) 公共表A  select * from A
			WithItem withItem = (WithItem) selectBody;
			if (withItem.getSelectBody() != null) {
				processSelectBody(withItem.getSelectBody());
			}
		} else {
			SetOperationList operationList = (SetOperationList) selectBody;
			if (operationList.getSelects() != null && operationList.getSelects().size() > 0) {
				List<SelectBody> plainSelects = operationList.getSelects();
				for (SelectBody plainSelect : plainSelects) {
					PlainSelect obj = null;
					if (plainSelect instanceof  PlainSelect) {
						obj = (PlainSelect)plainSelect;
					} else {
						continue;
					}
					processPlainSelect(obj);
				}
			}
			if (SetUtil.isEmpty(operationList.getOrderByElements())) {
				operationList.setOrderByElements(null);
			}
		}
		return null;
	}

	public TemplateObjBean processPlainSelect(PlainSelect plainSelect) {
		TemplateObjBean queryBean = new TemplateObjBean();
		List<TableView> views = null;
		Map<String, List<String>> map = new HashMap<>();
		if (!SetUtil.isEmpty(plainSelect.getGroupByColumnReferences())) {
			List<String> list = getGroupByKey(plainSelect);
			map.put("GroupBy", list);
		}
		if (plainSelect.getHaving() == null) {
			plainSelect.setGroupByColumnReferences(null);
		}
		if (plainSelect.getLimit() == null) {
			plainSelect.setLimit(null);
		}
		if (!SetUtil.isEmpty(plainSelect.getSelectItems())) {
			views = getTableViews(plainSelect);
		}
		if (!SetUtil.isEmpty(plainSelect.getOrderByElements())) {
			List<String> list = getOrderByKey(plainSelect);
			map.put("OrderBy", list);
		}
		handelTableView(views, map);
		Template template = getTemplate(plainSelect);
		queryBean.setTableViews(views);
		queryBean.setTemplate(template);
		return queryBean;
	    /*if (plainSelect.getFromItem() != null) {
	        processFromItem(plainSelect.getFromItem());  // assignment_form a  from主表
	    }
	    if (plainSelect.getJoins() != null && plainSelect.getJoins().size() > 0) {
	    	List<Join> joins = plainSelect.getJoins(); // 关联表
	    	processJoinItem(joins);
	    }  */
	}
	public void processJoinItem(List<Join> joins) {
		for (Join join : joins) {
			if (join.getRightItem() != null) {
				processFromItem(join.getRightItem());
			}
		}
	}
	public void processFromItem(FromItem fromItem) {
		if (fromItem instanceof SubJoin) {
			SubJoin subJoin = (SubJoin) fromItem;
			if (subJoin.getJoinList() != null && !subJoin.getJoinList().isEmpty()) {
				for (Join join:subJoin.getJoinList()) {
					if (join.getRightItem() != null) {
						processFromItem(join.getRightItem());
					}
				}

			}
			if (subJoin.getLeft() != null) {
				processFromItem(subJoin.getLeft());
			}
		} else if (fromItem instanceof SubSelect) {  //left join(select * from tab1) t

		} else if (fromItem instanceof ValuesList) {

		} else if (fromItem instanceof LateralSubSelect) {
			LateralSubSelect lateralSubSelect = (LateralSubSelect) fromItem;
			if (lateralSubSelect.getSubSelect() != null) {
				SubSelect subSelect = (SubSelect) (lateralSubSelect.getSubSelect());
				if (subSelect.getSelectBody() != null) {
					processSelectBody(subSelect.getSelectBody());
				}
			}
		}
		//Table时不用处理
	}

	public boolean orderByHashParameters(List<OrderByElement> orderByElements) {
		if (orderByElements == null) {
			return false;
		}
		for (OrderByElement orderByElement : orderByElements) {
			if (orderByElement.toString().toUpperCase().contains("?")) {
				return true;
			}
		}
		return false;
	}
	private List<TableView> getTableViews(PlainSelect plainSelect) {
		List<SelectItem> selectItems = plainSelect.getSelectItems();
		if (!SetUtil.isEmpty(selectItems)) {
			List<TableView> list = new ArrayList<>();
			for (int i = 0; i < selectItems.size(); i++) {
				list.add(getKeyAndAlias(selectItems.get(i),i));
			}
			return list;
		}
		return null;
	}
	private TableView getKeyAndAlias(SelectItem selectItem,Integer index) {
		String item = selectItem.toString().trim().replaceAll("\\s[a-zA-Z]{2}\\s", " AS ");
		String [] items = item.split(" AS ");
		TableView view = null;
		for (int i = 0; i < items.length; i++) {
			String str = items[i].replace(" ", "");
			if (!QueryStringUtil.isEmpty(str)) {
				if (view == null) {
					view = new TableView();
					view.setOrderIndex(index + 1);
					view.setKey(str);
					view.setDefaultView(1);
					view.setOrderby(0);
					view.setGroupby(0);
				}
				view.setAlias(str);
				view.setI18nKey(str);
			}

		}
		return view;
	}
	private List<String> getGroupByKey(PlainSelect plainSelect) {
		List <Expression> list = plainSelect.getGroupByColumnReferences();
		if (!SetUtil.isEmpty(list)) {
			List<String> lists = new ArrayList<>();
			for (Expression expression : list) {
				lists.add(expression.toString());
			}
			return lists;
		}
		return null;
	}
	private List<String> getOrderByKey(PlainSelect plainSelect) {
		List <OrderByElement> list = plainSelect.getOrderByElements();
		if (!SetUtil.isEmpty(list)) {
			List<String> lists = new ArrayList<>();
			for (OrderByElement element : list) {
				lists.add(element.getExpression().toString());
			}
			return lists;
		}
		return null;
	}
	private List<TableView> handelTableView(List<TableView> tableView,Map<String, List<String>> map) {
		if (SetUtil.isEmpty(map)) {
			return tableView;
		}
		if (map.containsKey("GroupBy")) {
			for (TableView view : tableView) {
				for (String key : map.get("GroupBy")) {
					if (QueryStringUtil.getUpperStr(view.getKey()).equals(QueryStringUtil.getUpperStr(key))) {
						view.setGroupby(1);
					}
				}
			}
		}
		if (map.containsKey("OrderBy")) {
			for (TableView view : tableView) {
				for (String key : map.get("OrderBy")) {
					if (QueryStringUtil.getUpperStr(view.getKey()).equals(QueryStringUtil.getUpperStr(key))) {
						view.setOrderby(1);
					}
				}
			}
		}
		return tableView;
	}

	public Template getTemplate(PlainSelect plainSelect) {
		Template template = new Template();
		Expression whereArea = (Expression) plainSelect.getWhere();
		template.setWhereArea((whereArea == null? "":" WHERE "+whereArea.toString()));
		return template;

	}
	private List<TableConditionBean> getTableConditions(List<String> statements) {
		if (SetUtil.isEmpty(statements)) {
			return null;
		}
		List<TableConditionBean> tableConditions = new ArrayList<>();
		for (String string : statements) {
			tableConditions.add(getTableCondition(string));
		}
		return tableConditions;
	}
	private TableConditionBean getTableCondition(String state) {
		if (QueryStringUtil.isEmpty(state)) {
			return null;
		}
		Expression expression;
		try {
			TableConditionBean tableCondition = new TableConditionBean();
			expression = CCJSqlParserUtil.parseCondExpression(state);
			if (expression instanceof OrExpression) {
				getTableOp(expression,tableCondition," OR ");
			} else if (expression instanceof AndExpression) {
				getTableOp(expression,tableCondition," AND ");
			} else {
				getTableOp(expression,tableCondition,"");
			}
			return tableCondition;
		} catch (JSQLParserException e) {
			e.printStackTrace();
		}
		return null;
	}
	private static TableConditionBean getTableOp(Expression expression,TableConditionBean tableCondition,String type) {
		if (expression == null) {
			return null;
		}
		List<TableOpBean> ops = new ArrayList<>();
		TableOpBean op = new TableOpBean();
		String key = null;
		if (expression instanceof EqualsTo) {
			op.setOpTypId(SqlConstants.OpType.eq);
			EqualsTo equalsTo = (EqualsTo) expression;
			key = QueryStringUtil.getUpperStr(equalsTo.getLeftExpression().toString());
		} else if (expression instanceof MinorThan) {
			op.setOpTypId(SqlConstants.OpType.lt);
			MinorThan minorThan = (MinorThan) expression;
			key = QueryStringUtil.getUpperStr(minorThan.getLeftExpression().toString());
		} else if (expression instanceof GreaterThan) {
			op.setOpTypId(SqlConstants.OpType.gt);
			GreaterThan greaterThan = (GreaterThan) expression;
			key = QueryStringUtil.getUpperStr(greaterThan.getLeftExpression().toString());
		} else if (expression instanceof LikeExpression) {
			op.setOpTypId(SqlConstants.OpType.Like);
			tableCondition.setQuick(1);
			LikeExpression likeExpression = (LikeExpression) expression;
			key = QueryStringUtil.getUpperStr(likeExpression.getLeftExpression().toString());
		} else if (expression instanceof InExpression) {
			tableCondition.setFiledType(SqlConstants.FieldType.List);
			InExpression inExpression = (InExpression) expression;
			key = QueryStringUtil.getUpperStr(inExpression.getLeftExpression().toString());
		} else if (expression instanceof IsNullExpression) {
			op.setOpTypId(SqlConstants.OpType.is);
			IsNullExpression isNullExpression = (IsNullExpression) expression;
			key = QueryStringUtil.getUpperStr(isNullExpression.getLeftExpression().toString());
		} else if (expression instanceof GreaterThanEquals) {
			op.setOpTypId(SqlConstants.OpType.ge);
			GreaterThanEquals greaterThanEquals = (GreaterThanEquals) expression;
			key = QueryStringUtil.getUpperStr(greaterThanEquals.getLeftExpression().toString());
		} else if (expression instanceof MinorThanEquals) {
			op.setOpTypId(SqlConstants.OpType.le);
			MinorThanEquals  minorThanEquals = (MinorThanEquals) expression;
			key = QueryStringUtil.getUpperStr(minorThanEquals.getLeftExpression().toString());
		} else if (expression instanceof NotEqualsTo) {
			op.setOpTypId(SqlConstants.OpType.not_eq);
			NotEqualsTo notEqualsTo = (NotEqualsTo) expression;
			key = QueryStringUtil.getUpperStr(notEqualsTo.getLeftExpression().toString());
		} else if (QueryStringUtil.getUpperStr(expression.toString()).contains("IS NOT NULL")) {
			op.setOpTypId(SqlConstants.OpType.is_not);
			key = QueryStringUtil.getUpperStr(expression.toString().split("IS NOT NULL")[0]);
		} else if (expression instanceof ExistsExpression) {

		} else if (expression instanceof Between) {
			op.setOpTypId(SqlConstants.OpType.between_in);
			Between between = (Between) expression;
			key = QueryStringUtil.getUpperStr(between.getLeftExpression().toString());
		}
		getKeyAndAlias(key,tableCondition);
		tableCondition.setDefaultOp(op.getOpTypId());
		op.setOpName(SqlConstants.OpType.getOpName(op.getOpTypId()));
		ops.add(op);
		tableCondition.setTableOps(ops);
		return tableCondition;
	}
	public static TableConditionBean getKeyAndAlias(String expression,TableConditionBean tableConditionBean) {
		if (QueryStringUtil.isEmpty(expression)) {
			return tableConditionBean;
		}
		String[] key = expression.split("\\.");
		if (key.length>1) {
			tableConditionBean.setAlias(key[1]);
		} else {
			tableConditionBean.setAlias(expression);
		}
		tableConditionBean.setKey(expression);
		tableConditionBean.setI18nKey(expression);
		return tableConditionBean;
	}

	public static String[]  handelWhereArea(String leftValue) {
		return handelWhereArea(leftValue, "");
	}
	public static String handelWhere(String whereArea,List<String> conditionItems) {
		for (String string : conditionItems) {
			whereArea = whereArea.replace(string, handelWhereArea(string)[0]);
		}

		return whereArea;
	}

	public static String[] handelWhereArea(String valueStart,String valueEnd) {
		String [] arr = new String[2];
		String value1 = valueStart;
		String value2 = valueEnd;
		if (valueStart.contains(SqlFormateUtil.prefix)) {
			valueStart = getValue(valueStart);
			if (valueStart.equals("false")) {
				valueStart = SqlFormateUtil.replaceBack(value1);
			}
		} else {
			valueStart = SqlFormateUtil.replaceBack(value1);
		}
		arr[0] = valueStart;
		if (!QueryStringUtil.isEmpty(valueEnd)) {
			if (valueEnd.contains(SqlFormateUtil.prefix)) {
				valueEnd = getValue(valueEnd);
				if (valueEnd.equals("false")) {
					valueEnd = SqlFormateUtil.replaceBack(value2);
				}
			} else {
				valueEnd = SqlFormateUtil.replaceBack(value2);
			}
			arr[1] = valueEnd;
		}
		return arr;

	}

	@SuppressWarnings("all")
	public static String getValue(String leftExpression) {
		try {
			leftExpression = leftExpression.replace(SqlFormateUtil.prefix, "");
			leftExpression = leftExpression.replace(SqlFormateUtil.suffix, "");
			if (leftExpression.split("\\.").length < 2) {
				return "false";
			}
			String shortName = leftExpression.split("\\.")[0];

			String fieldName = leftExpression.split("\\.")[1];
			if (BaseConditionValue.INSTANCE.map ==null) {
				Map<String, Class> maps = new HashMap<String, Class>();
				maps.put("currentUser", UserConditionValue.class);
				BaseConditionValue.INSTANCE.map =  maps;
			}
			Class class1 = BaseConditionValue.INSTANCE.map.get(shortName);
			if (class1 != null) {
				Object object = class1.newInstance();
				if (object instanceof AbstractConditionValue) {
					String valule = ((AbstractConditionValue) object).gethandledValue(fieldName);
					return valule;
				} else {
					return "false";
				}

			} else {
				return "false";
			}
		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return leftExpression;

	}
	public static void main(String[] args) {

	}
}
